# database/models.py
from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()


class UserInfo(Base):
    __tablename__ = 'userinfo'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(20), unique=True, index=True)  # 用户名/银行卡号
    password = Column(String(100))  # 加密后的密码
    real_name = Column(String(50))  # 真实姓名
    id_card = Column(String(20))  # 身份证号
    phone = Column(String(20))  # 电话号码
    address = Column(String(100))  # 地址
    role = Column(String(10), default='customer')  # 角色: customer, admin
    created_at = Column(DateTime, default=datetime.now)

    # 关联银行卡
    cards = relationship("CardInfo", back_populates="user")


class CardInfo(Base):
    __tablename__ = 'cardinfo'

    id = Column(Integer, primary_key=True, index=True)
    card_number = Column(String(20), unique=True, index=True)  # 银行卡号
    user_id = Column(Integer, ForeignKey('userinfo.id'))  # 用户ID
    card_type = Column(Integer)  # 存款类型(参考前端的存款类型)
    balance = Column(Float, default=0.0)  # 余额
    status = Column(String(20), default='normal')  # 状态: normal, lost, stolen, damaged
    currency = Column(String(10), default='RMB')  # 币种
    open_date = Column(DateTime, default=datetime.now)  # 开户日期
    due_date = Column(DateTime)  # 到期日(定期存款)

    # 关联用户
    user = relationship("UserInfo", back_populates="cards")
    # 关联交易记录
    transactions = relationship("TradeInfo", back_populates="card")


class Deposit(Base):
    __tablename__ = 'deposit'

    id = Column(Integer, primary_key=True, index=True)
    deposit_type = Column(Integer, unique=True, index=True)  # 存款类型编号
    deposit_name = Column(String(50))  # 存款类型名称
    description = Column(String(200))  # 存款类型描述
    interest_rate = Column(Float)  # 利率(实际应用中需要添加)


class TradeInfo(Base):
    __tablename__ = 'tradeinfo'

    id = Column(Integer, primary_key=True, index=True)
    card_number = Column(String(20), ForeignKey('cardinfo.card_number'))  # 银行卡号
    trade_type = Column(String(20))  # 交易类型: deposit, withdraw, transfer
    amount = Column(Float)  # 交易金额
    other_card = Column(String(20))  # 对方账户(转账时使用)
    remark = Column(String(100))  # 备注
    trade_time = Column(DateTime, default=datetime.now)  # 交易时间
    status = Column(String(20), default='completed')  # 状态: completed, pending

    # 关联银行卡
    card = relationship("CardInfo", back_populates="transactions")